//
// Copyright (c) 2014, 保留所有权利。
//
// MergeBackup.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午5:47:43
//
// 
//
package com.vanke.agent.tools.merge;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.bean.CstInfo;
import com.vanke.agent.common.Cache;
import com.vanke.agent.common.CstUtil;
import com.vanke.agent.common.log.LoggerUtil;

/**
 *
 */
public class MergeBackup {
	static Logger log = LoggerUtil.getLoggerByName("backup_merge");
	static final DateFormat dateFormat = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss.S");

	private Set<String> allTables = new HashSet<String>();

	public void backup(CstInfo cstFrom, CstInfo cstTo,
			Map<Long, Set<String>> cstMobileMap,
			Map<Long, Set<String>> cstCardIdMap,
			Map<Long, Set<String>> cstOriGuidMap) {
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);

		allTables.addAll(Cache.loadTables(con, "")); //

		String uuid = uuid(con);
		if (uuid == null) {
			return;
		}
		Statement stmt = null;
		try {
			con.setAutoCommit(false);
			stmt = con.createStatement();
			//	1. 备份客户主档
			log.info("备份主档");
			backupInfo(stmt, uuid, cstFrom, cstTo);
			//	2. 备份映射关系
			log.info("备份手机映射");
			backupMobileMapping(stmt, uuid, cstMobileMap);
			log.info("备份身份证映射");
			backupCardIdMapping(stmt, uuid, cstCardIdMap);
			log.info("备份明源GUID映射");
			backupOriGUIDMapping(stmt, uuid, cstOriGuidMap);
			//	3. 备份推荐关系
			log.info("备份推荐关系");
			backupRecommend(stmt, uuid, cstFrom);
			backupRecommend(stmt, uuid, cstTo);
			//	4. 备份跟进记录
			log.info("备份跟进记录");
			backupFollow(stmt, uuid, cstFrom);
			//	5. 备份订购关系
			log.info("备份订购关系");
			backupProductOrder(stmt, uuid, cstFrom);
			//	6. 备份会员信息
			log.info("备份会员信息");
			backupMember(stmt, uuid, cstFrom);

			log.info("执行备份");
			stmt.executeBatch();
			log.info("提交备份");
			con.commit();
			log.info("备份完成");
		} catch (Exception e) {
			e.printStackTrace();
			log.error("backup cst error:" + e);
		} finally {
			ADataSource.close(null, stmt, con);
		}
	} //~backup

	/** 备份客户主档信息 */
	public void backupInfo(Connection con, String uuid, CstInfo cstFrom,
			CstInfo cstTo) {
		Date time = new Date();
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			backupInfo(stmt, uuid, cstFrom, M.MERGE_DROPPED, time);
			backupInfo(stmt, uuid, cstTo, M.MERGE_RETAIN, time);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("备份客户信息异常：" + e);
		} finally {
			ADataSource.close(stmt);
		}
	} //~backupInfo

	/** 生成UUID */
	public String uuid(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select uuid()");
			rs.next();
			return rs.getString(1).toUpperCase();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("generate uuid error：" + e);
			return null;
		} finally {
			ADataSource.close(rs, stmt, null);
		}
	} //~dbuuid
		//////////////////////////////////////////////////////////////////////////

	/** 备份客户主档信息 */
	protected void backupInfo(Statement stmt, String uuid, CstInfo cstFrom,
			CstInfo cstTo) throws SQLException {
		Date time = new Date();
		backupInfo(stmt, uuid, cstFrom, 0, time);
		backupInfo(stmt, uuid, cstTo, 1, time);
	} //~backupInfo

	/*
	 * 备份主档信息
	 */
	private void backupInfo(Statement stmt, String uuid, CstInfo cst,
			int retain, Date time) throws SQLException {
		String fromTable = CstUtil.retrieveCustomerTable(cst.getCstGUID());
		String fmt = "insert into backup_cst(%s,retain,merge_time,batch_no)";
		String insert = String.format(fmt, C.DC_FIELDS_CST);
		fmt = "(select %s,%s,'%s','%s' from %s where CstGUID=%s)";
		String select = String.format(fmt, C.DC_FIELDS_CST, retain,
				dateFormat.format(time), uuid, fromTable, cst.getCstGUID());
		String sql = insert + select;
		stmt.addBatch(sql);
		log.info(sql);
	} //~backupInfo(Statement stmt, String uuid, CstInfo cst, int retain, Date time)

	/** 备份客户手机映射 */
	protected void backupMobileMapping(Statement stmt, String uuid,
			Map<Long, Set<String>> mobileMap) throws SQLException {
		Iterator<Entry<Long, Set<String>>> iter;
		iter = mobileMap.entrySet().iterator();
		String fmt = "insert into backup_mapping(identity,type,CstGUID,batch_no) values('%s',1,%s,'%s')";
		while (iter.hasNext()) {
			Entry<Long, Set<String>> entry = iter.next();
			Set<String> mobiles = entry.getValue();
			for (String mobile : mobiles) {
				String sql = String.format(fmt, mobile, entry.getKey(), uuid);
				stmt.addBatch(sql);
				log.info(sql);
			} // end for
		} // end while
	} //~backupMobileMapping

	/** 备份客户身份证映射 */
	protected void backupCardIdMapping(Statement stmt, String uuid,
			Map<Long, Set<String>> cardMap) throws SQLException {
		Iterator<Entry<Long, Set<String>>> iter;
		iter = cardMap.entrySet().iterator();
		String fmt = "insert into backup_mapping(identity,type,CstGUID,batch_no) values('%s',2,%s,'%s')";
		while (iter.hasNext()) {
			Entry<Long, Set<String>> entry = iter.next();
			Set<String> mobiles = entry.getValue();
			for (String mobile : mobiles) {
				String sql = String.format(fmt, mobile, entry.getKey(), uuid);
				stmt.addBatch(sql);
				log.info(sql);
			} // end for
		} // end while
	} //~backupCardIdMapping

	/** 备份客户明源GUID映射 */
	protected void backupOriGUIDMapping(Statement stmt, String uuid,
			Map<Long, Set<String>> guidMap) throws SQLException {
		Iterator<Entry<Long, Set<String>>> iter;
		iter = guidMap.entrySet().iterator();
		String fmt = "insert into backup_mapping(identity,type,CstGUID,batch_no) values('%s',3,%s,'%s')";
		while (iter.hasNext()) {
			Entry<Long, Set<String>> entry = iter.next();
			Set<String> mobiles = entry.getValue();
			for (String mobile : mobiles) {
				String sql = String.format(fmt, mobile, entry.getKey(), uuid);
				stmt.addBatch(sql);
				log.info(sql);
			} // end for
		} // end while
	} //~backupOriGUIDMapping

	/** 备份客户推荐关系 */
	protected void backupRecommend(Statement stmt, String uuid, CstInfo cst)
			throws SQLException {
		//03. 创建推荐关系备份表(backup_recommend)，表结构如下：
		//	GUID bigint(20),		// 对应GUID
		//	CstMobile varchar(16),	// 对应CstMobile
		//	CstGUID bigint(20),		// 数据中心客户GUID，对应保留或丢弃客户的ID
		//	batch_no varchar(64),	// 对应backup_cst:batch_no
		String fmt = "insert into backup_recommend(GUID,CstMobile,CstGUID,batch_no)(select GUID,CstMobile,CstGUID,'%s' from %s where CstGUID=%s)";
		String prefix = "prm_Agent_Cst_Recommed_";

		long cstGuid = cst.getCstGUID();
		String table = CstUtil.calcCstSplitTable(prefix, cstGuid);
		if (allTables.contains(table)) {
			String sql = String.format(fmt, uuid, table, cstGuid);
			stmt.addBatch(sql);
			log.info(sql);
		} else {
			log.info("no recommend for " + cst);
		}
	} //~backupRecommend

	/** 备份客户跟进记录 */
	protected void backupFollow(Statement stmt, String uuid, CstInfo cst)
			throws SQLException {
		//04.创建跟进记录备份表(backup_follow)，结构如下：
		//	PkIndex bigint(20),		// 对应PkIndex
		//	followType int,			// 对应followType
		//	MyGUID varchar(64),		// 对应MyGUID
		//	TradeGUID varchar(64),	// 对应TradeGUID
		//	CstGUID bigint(20),		// 数据中心客户GUID，对应保留或丢弃客户的ID
		//	batch_no varchar(64),	// 对应backup_cst:batch_no
		String fmt = "insert into backup_follow(PkIndex,followType,MyGUID,TradeGUID,CstGUID,batch_no)(select PkIndex,followType,MyGUID,TradeGUID,CstGUID,'%s' from %s where CstGUID=%s)";
		String prefix = "cst_FollowRecord_";

		long cstGuid = cst.getCstGUID();
		String table = CstUtil.calcCstSplitTable(prefix, cstGuid);
		if (allTables.contains(table)) {
			String sql = String.format(fmt, uuid, table, cstGuid);
			stmt.addBatch(sql);
			log.info(sql);
		} else {
			log.info("no follow record for " + cst);
		}
	} //~backupFollow

	/** 备份客户订购关系 */
	protected void backupProductOrder(Statement stmt, String uuid, CstInfo cst)
			throws SQLException {
		//	05.	创建订购关系备份表(backup_product_order)，结构如下：
		//		POrderGUID bigint(20),	// 对应POrderGUID
		//		ProductType tinyint,	// 对应ProductType
		//		ProductCode varchar(128),// 对应ProductCode
		//		CstGUID bigint(20),		// 数据中心客户GUID，对应保留或丢弃客户的ID
		//		batch_no varchar(64),	// 对应backup_cst:batch_no
		String fmt = "insert into backup_product_order(POrderGUID,ProductType,ProductCode,CstGUID,batch_no)(select POrderGUID,ProductType,ProductCode,CstGUID,'%s' from %s where CstGUID=%s)";
		String prefix = "cst_Product_Order_";

		long cstGuid = cst.getCstGUID();
		String table = CstUtil.calcCstSplitTable(prefix, cstGuid);
		if (allTables.contains(table)) {
			String sql = String.format(fmt, uuid, table, cstGuid);
			stmt.addBatch(sql);
			log.info(sql);
		} else {
			log.info("no product order for " + cst);
		}
	} //~backupProductOrder

	/** 备份会员信息 */
	protected void backupMember(Statement stmt, String uuid, CstInfo cst)
			throws SQLException {
		//06.	创建会员备份表(backup_member)，表结构包含基础表所有字段外，增加一个字段：
		//	MemGUID bigint(20),		// 对应MemGUID
		//	OriMemGUID varchar(64),	// 对应OriMemGUID
		//	CstGUID bigint(20),		// 数据中心客户GUID，对应保留或丢弃客户的ID
		//	batch_no varchar(64),	// 对应backup_cst:batch_no
		String fmt = "insert into backup_member(MemGUID,OriMemGUID,CstGUID,batch_no)(select MemGUID,OriMemGUID,CstGUID,'%s' from %s where CstGUID=%s)";
		String prefix = "cst_Member_";

		long cstGuid = cst.getCstGUID();
		String table = CstUtil.calcCstSplitTable(prefix, cstGuid);
		if (allTables.contains(table)) {
			String sql = String.format(fmt, uuid, table, cstGuid);
			stmt.addBatch(sql);
			log.info(sql);
		} else {
			log.info("no member for " + cst);
		}
	} //~backupMember
} ///~MergeBackup
